<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2016/6/16
 * Time: 14:55
 */
namespace Home\Model;
use Think\Model;
class InstallModel extends Model{
    // 空 Model
    protected $myModel = NULL;
    protected $ip = array("'183.63.130.26'","'118.26.187.88'");
    // 初始化 Model
    public function __construct()
    {
        $this -> myModel = new Model();
    }

    public function getList($table_name,$where = NUll,$limit = 0,$group_by="",$order_file = "",$order_type = "ASC") {
        $sql = "SELECT * FROM %s";
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql .= "WHERE ip NOT IN(".$ips.") ";
        if($where && is_array($where)){
            foreach($where as $key => $wh){
                $sql .= " AND ".$key ." = " . $wh;
            }
        }

        if($group_by){
            $sql .= " GROUP BY ".$group_by;
        }
        if($order_file){
            $sql .= " ORDER BY ".$order_file." ".$order_type;
        }
        if($limit > 0){
            $sql .= " LIMIT ".$limit;
        }
        $sql = sprintf($sql,$table_name);
        Log::write('getList:'.$sql,'DEBUG');
        return $this -> myModel -> query($sql);
    }

    public function getCounts($table_name,$where,$group_by){
        $sql = "SELECT COUNT(*),$group_by FROM %s";
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql .= "WHERE ip NOT IN(".$ips.") ";
        if($where && is_array($where)){
            foreach($where as $key => $wh){
                $sql .= " AND ".$key ." = " . $wh;
            }
        }
        if($group_by){
            $sql .= " GROUP BY ".$group_by;
        }
        $sql = sprintf($sql,$table_name);
        return $this -> myModel -> query($sql);
    }
    // 根据指定的列作为键值，返回对应数据
    public function getCountsByField($table_name,$kfield){
        $where = array('ip' => array('NOT IN', array_map('filterArray',$this->ip)));
        $result = $this -> myModel -> table($table_name) -> where($where) -> group($kfield) -> getField("$kfield,COUNT(DISTINCT uuid)");
        return $result;
    }
    // 获取当日安装后卸载的数据 active == 3 表示 日活
    public function getInstallUnCount($table_name){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT COUNT(DISTINCT active) AS a_count,uuid FROM %s  WHERE active <> 3 and ip NOT IN(%s) GROUP BY uuid HAVING  a_count > 1 ";
        $sql = sprintf($sql,$table_name, $ips);
        return $this -> myModel -> query($sql);
    }
    // 按渠道获取设备不同状态的数据统计
    public function getCountsByChannel($table_name){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT count(DISTINCT uuid) AS c_count,active,channel_id from %s WHERE ip NOT IN(".$ips.") group by channel_id,active";
        $sql = sprintf($sql,$table_name);
        return $this -> myModel -> query($sql);
    }
    //获取当日安装后卸载的设备数按照channel 返回
    public function getInstallUnCountByChannel($table_name){
        $sql = "SELECT channel_id FROM ".$table_name." GROUP BY channel_id";
        $ret = $this -> myModel -> query($sql);
        $ips = rtrim(implode($this -> ip, ','),',');
        $channel_count = array();
        if($ret){
            foreach($ret as $channel_info){
                $channel_id = ($channel_info['channel_id'])?:"0000";
                $channel_count[$channel_id] = 0;
                $sql = "SELECT COUNT(DISTINCT active) AS a_count,uuid FROM ".$table_name."  WHERE `active` <> 3 AND channel_id = '".$channel_info['channel_id']."' and ip NOT IN(".$ips.") GROUP BY uuid HAVING  a_count > 1";
                $result = $this -> myModel -> query($sql);
                if($result){
                    $channel_count[$channel_id] = count($result);
                }
            }
        }
        return $channel_count;
    }
    //获取固定channel的某一天的设备统计数据
    public function getStatByChannel($table_name,$channel_id){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT count(DISTINCT uuid) as u_count,active from ".$table_name."   where channel_id = '".$channel_id."' and ip NOT IN(".$ips.") group by active";
        return $this -> myModel -> query($sql);
    }
    //获取 固定channel 中某一天的 当日安装后卸载的设备数量
    public function getInstallUnByChannelDay($table_name,$channel_id){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT COUNT(DISTINCT active) AS a_count,uuid FROM ".$table_name."  WHERE `active` <> 3 AND channel_id = '".$channel_id."' GROUP BY uuid HAVING  a_count > 1";
        return count($this -> myModel -> query($sql));
    }
    //用于总量统计脚本，获取每日的各种设备状态的数据
    public function getDateData($table_name){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT COUNT(DISTINCT uuid) AS u_count,active FROM ".$table_name." WHERE ip NOT IN(".$ips.") GROUP BY active";
        return $this -> myModel -> query($sql);
    }
    //用于总量统计脚本，获取每日的 设备安装后又卸载的总量
    public function getInstallUnAllData($table_name){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT COUNT(DISTINCT active) AS a_count,uuid FROM ".$table_name."  WHERE `active` <> 3 and ip NOT IN(".$ips.") GROUP BY uuid HAVING  a_count > 1";
        return count($this -> myModel -> query($sql));
    }
    //用于 统计脚本中 一天 按channel 分组的  install active uninstall 状态的数据
    public function getDayDataGroupChannel($table_name){
        $ips = rtrim(implode($this -> ip, ','),',');
        $sql = "SELECT COUNT(DISTINCT uuid) AS c_count,active,channel_id FROM %s WHERE ip NOT IN (".$ips.") GROUP BY channel_id,active";
        $sql = sprintf($sql,$table_name);
        return $this -> myModel -> query($sql);
    }
    //用于 统计脚本中 一天 按channel 分组的  installun 状态的数据
    public function getDayIUDataGroupChannel($table_name){

    }
}